﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Common;
using Qianli.DirSir.Model;
using System.Data;

namespace Qianli.DirSir.DAL
{
    public class CustomersInfoDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 显示客户信息
        /// </summary>
        /// <returns></returns>
        public List<CustomersInfo> ShowCustomersInfo()
        {
            string sql = "ShowCustomersInfo";

            DataTable table = helper.ExecDataTable_Proc(sql);
            List<CustomersInfo> list = new List<CustomersInfo>();
            foreach (DataRow item in table.Rows)
            {

                CustomersInfo c = new CustomersInfo();
                c.CityID = Convert.ToInt32(item["CityID"]);
                c.CustomersID = Convert.ToInt32(item["CustomersID"]);
                c.CustomersCoding = item["CustomersCoding"].ToString();
                c.CustomersName = item["CustomersName"].ToString();
                c.Phone = item["Phone"].ToString();
                c.CityName = item["CityName"].ToString();
                list.Add(c);
            }
            return list;

        }

        /// <summary>
        /// 显示城市信息
        /// </summary>
        /// <returns></returns>
        public List<CityInfo> ShowCity()
        {
            string sql = "ShowCity";

            DataTable table = helper.ExecDataTable_Proc(sql);
            List<CityInfo> list = new List<CityInfo>();
            foreach (DataRow item in table.Rows)
            {
                CityInfo c = new CityInfo();
                c.CityID = Convert.ToInt32(item["CityID"]);
                c.CityName = item["CityName"].ToString();
                list.Add(c);
            }
            return list;

        }

        /// <summary>
        /// 添加客户信息
        /// </summary>
        /// <param name="cust"></param>
        /// <returns></returns>
        public int AddCustomersInfo(CustomersInfo cust)
        {
            if (cust.CustomersCoding != "")
            {
                cust.CustomersCoding = System.Guid.NewGuid().ToString();
                string sql = "insert into CustomersInfo(CustomersCoding,CustomersName,Phone,CityID) values('" + cust.CustomersCoding + "','" + cust.CustomersName + "','" + cust.Phone + "'," + cust.CityID + ")";
                return helper.ExecNonquery(sql);
            }
            else {
                string sql = "insert into CustomersInfo(CustomersCoding,CustomersName,Phone,CityID) values('" + cust.CustomersCoding + "','" + cust.CustomersName + "','" + cust.Phone + "'," + cust.CityID + ")";
                return helper.ExecNonquery(sql);
            }
            //Guid g = new Guid();
            //cust.CustomersCoding = g.ToString();
            //string sql = "insert into CustomersInfo(CustomersCoding,CustomersName,Phone,CityID) values('" + cust.CustomersCoding + "','" + cust.CustomersName + "','" + cust.Phone + "'," + cust.CityID + ")";
            //return helper.ExecNonquery(sql);
        }
        /// <summary>
        /// 删除客户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCustomersInfo(int id)
        {
            string sql = "delete from CustomersInfo where CustomersID=" + id + "";
            return helper.ExecNonquery(sql);
        }


        /// <summary>
        /// 根据id获取客户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>

        public CustomersInfo GetCustomersInfoByid(int id)
        {
            string sql = "select CustomersID,CustomersCoding,CustomersName,Phone,CityInfo.CityName,CityInfo.CityID  from CustomersInfo join CityInfo on CustomersInfo.CityID=CityInfo.CityID where CustomersID=" + id + "";
            DataTable table = helper.ExecDataTable(sql);
            CustomersInfo c = new CustomersInfo();


                c.CustomersID = Convert.ToInt32(table.Rows[0]["CustomersID"]);
                c.CustomersCoding = (table.Rows[0]["CustomersCoding"]).ToString();
                c.CustomersName = (table.Rows[0]["CustomersName"]).ToString();
                c.Phone = (table.Rows[0]["Phone"]).ToString();
                c.CityName = (table.Rows[0]["CityName"]).ToString();
                c.CityID = Convert.ToInt32(table.Rows[0]["CityID"]);

            
            return c;
        }
        /// <summary>
        /// 修改客户信息
        /// </summary>
        /// <param name="cust"></param>
        /// <returns></returns>
        public int UptCustomersInfo(CustomersInfo cust)
        {
            string sql = "update CustomersInfo set  CustomersCoding='" + cust.CustomersCoding + "',CustomersName='" + cust.CustomersName + "',Phone='" + cust.Phone + "',CityID='" + cust.CityID + "' where CustomersID='" + cust.CustomersID + "'";
            return helper.ExecNonquery(sql);
        }
        //<summary>
        //根据姓名搜索
        //</summary>
        //<param name="CustomersName"></param>
        //<returns></returns>
        public List<CustomersInfo> GetCustomersInfoByCustomersName(string CustomersName)
        {
            string sql = "select CustomersID,CustomersCoding,CustomersName,Phone,CityInfo.CityName from CustomersInfo join CityInfo on CustomersInfo.CityID=CityInfo.CityID where CustomersName like '%" + CustomersName + "%'";

            DataTable table = helper.ExecDataTable(sql);
            List<CustomersInfo> list = new List<CustomersInfo>();
            foreach (DataRow item in table.Rows)
            {
                CustomersInfo c = new CustomersInfo();
                c.CustomersID = Convert.ToInt32(item["CustomersID"]);
                c.CustomersCoding = item["CustomersCoding"].ToString();
                c.CustomersName = item["CustomersName"].ToString();
                c.Phone = item["Phone"].ToString();
                c.CityName = item["CityName"].ToString();
                list.Add(c);
            }
            return list;

        }
    }
}
